-- given a DocumentKey or a DocumentVersionKey, gets the first path to it (there should be only one)
CREATE FUNCTION [dbo].[asi_PublishedDocumentPath](@key uniqueidentifier)
RETURNS nvarchar(4000)
AS
BEGIN
   DECLARE @hierarchyKey uniqueidentifier
   DECLARE @rootHierarchyKey uniqueidentifier
   DECLARE @documentName nvarchar(100)
   DECLARE @path nvarchar(4000)

   SELECT TOP 1 @hierarchyKey = Hierarchy.HierarchyKey,
          @documentName = DocumentName, @rootHierarchyKey = Hierarchy.RootHierarchyKey
     FROM Hierarchy inner join HierarchyRoot on Hierarchy.RootHierarchyKey = HierarchyRoot.RootHierarchyKey
          inner join DocumentMain on Hierarchy.UniformKey = DocumentMain.DocumentVersionKey
    WHERE (DocumentMain.DocumentKey = @key
       OR Hierarchy.UniformKey = @key)
      AND (DocumentMain.DocumentStatusCode IN (40,50))
    ORDER BY DocumentMain.CreatedOn DESC

   IF @@ROWCOUNT > 0
   BEGIN
      SELECT @path =  [dbo].[asi_DocumentPathFromHierarchyKey](@hierarchyKey)
      IF @hierarchyKey <> @rootHierarchyKey
         SET @path = @path + '/' + @documentName      
   END

   RETURN @path
END

GO